using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections;
using Googler.BLL;

namespace Googler.DAL
{
	public class DAL_MsSql : IDataAcessLayerBaseInterface	
	{
		
		SqlConnection conn;
		public DAL_MsSql()
		{
			conn=new SqlConnection(Globals.ConnectionString);//
		}
        public DAL_MsSql(string connectionString)
        {
            this.conn = new SqlConnection(connectionString);
        }

 
	public void OpenConnection()
		{
			if(conn.State!=ConnectionState.Open)
				conn.Open();
		}
		public void CloseConnection()
		{
			if(conn.State!=ConnectionState.Closed)
				conn.Close();
		}
        public object ExecuteScalar(string procName, params object[] Params)
        {
            SqlCommand cmd = new SqlCommand(procName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (Params != null)
            {
                for (int i = 0; i < Params.Length; i += 2)
                {
                    cmd.Parameters.AddWithValue(Params[i].ToString(), GetValue(Params[i + 1]));
                }
            }
            return cmd.ExecuteScalar();
        }
        private object GetValue(object p)
        {
            if (p != null)
            {
                if (p.GetType() == typeof(DateTime))
                {
                    if (Convert.ToDateTime(p) < new DateTime(1900, 1, 1))
                        p = new DateTime(1900, 1, 1);
                }
                return p;
            }
            return DBNull.Value;
        }
		public object ExecuteScalar(string query)
		{
			SqlCommand cmd = new SqlCommand(query,conn);
			return cmd.ExecuteScalar();
		}
		public void ExecuteNonQuery(string procName,params object[] Params)
		{
			SqlCommand cmd = new SqlCommand(procName,conn);
			cmd.CommandType=CommandType.StoredProcedure;
			for(int i=0;i<Params.Length;i+=2)
			{
				cmd.Parameters.AddWithValue(Params[i].ToString(),GetValue(Params[i+1]));
			}
			
            //try
            //{
				cmd.ExecuteNonQuery();
            //}
            //catch(SqlException se)
            //{
            //    if(se.Number.Equals(547))
            //        throw new ObjectHasChildrenException();
            //        if(se.Number.Equals(2627))
            //            throw new RecordAlreadyExistException();
            //    else
            //        throw se;
            //}
		}

        public void ExecuteNonQuery(string procName)
        {
            SqlCommand cmd = new SqlCommand(procName, conn);
            //try
            //{
                cmd.ExecuteNonQuery();
            //}
            //catch (SqlException se)
            //{
            //    if (se.Number.Equals(547))
            //        throw new ObjectHasChildrenException();
            //    if (se.Number.Equals(2627))
            //        throw new RecordAlreadyExistException();
            //    else
            //        throw se;
            //}
        }

		
		public IDataReader ExecuteReader(string procName,params object[] Params)
		{
			SqlCommand cmd = new SqlCommand(procName,conn);
			cmd.CommandType=CommandType.StoredProcedure;
			if(Params!=null)
			{
				for(int i=0;i<Params.Length;i+=2)
				{
					cmd.Parameters.Add(Params[i].ToString(),GetValue(Params[i+1]));
				}
			}	
			return cmd.ExecuteReader();
		}

		public IDataReader ExecuteReader(string query)
		{
			SqlCommand cmd = new SqlCommand(query,conn);
			return cmd.ExecuteReader();
		}
		public DataSet ExecuteDataSet(string qurey)
		{
			SqlDataAdapter da=new SqlDataAdapter(qurey,conn);
			DataSet ds =new DataSet();
			da.Fill(ds);
			return ds;
		}
		public DataSet ExecuteDataSet(string procName,params object[] Params)
		{
			SqlDataAdapter da = new SqlDataAdapter(procName,conn);
			da.SelectCommand.CommandType=CommandType.StoredProcedure;
			if (Params != null)
			{
				for(int i=0;i<Params.Length;i+=2)
				{
					da.SelectCommand.Parameters.Add(Params[i].ToString(),GetValue(Params[i+1]));
				}
			}	
			DataSet ds=new DataSet();
			da.Fill(ds);
			return ds;
			
		}

		public DataTable ExecuteDataTable(string qurey)
		{
			SqlDataAdapter da=new SqlDataAdapter(qurey,conn);
			DataTable dt=new DataTable();
			da.Fill(dt);
			return dt;
		}
		public DataTable ExecuteDataTable(string procName,params object[] Params)
		{
			SqlDataAdapter da = new SqlDataAdapter(procName,conn);
			da.SelectCommand.CommandType=CommandType.StoredProcedure;
			if(Params != null)
			{
				for(int i=0;i<Params.Length;i+=2)
				{
					da.SelectCommand.Parameters.Add(Params[i].ToString(),GetValue(Params[i+1]));
				}
			}
			DataTable dt=new DataTable();
			da.Fill(dt);
			return dt;	
		}
		//		public static CollectionBase ExecuteCollection(Sqlconnection conn, string qurey)
		//		{
		//			SqlDataReader dr = new SqlDataReader();
		//			CollectionBase collection = new CollectionBase();
		//			da.Fill(collection);
		//			return collection;
		//		}
		//		public static CollectionBase ExecuteCollection(Sqlconnection conn,string procName,params object[] Params)
		//		{
		//			SqlDataAdapter da = new SqlDataAdapter(procName,conn);
		//			da.SelectCommand.CommandType=CommandType.StoredProcedure;
		//			if(Params != null)
		//			{
		//				for(int i=0;i<Params.Length;i+=2)
		//				{
		//					da.SelectCommand.Parameters.Add(Params[i].ToString(),Params[i+1]);
		//				}
		//			}
		//			CollectionBase collection = new CollectionBase();
		//			da.Fill(collection);
		//			return collection;
		//		}
	}
}


